package abstraction;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

/**Génération d'une base de donnée vide en cas de suppression de la précédente
 * 
 */
public class BDCours {
	
	private Connection connexion;

	/**
	 * driver JDBC
	 */
	private String jdbcDriver = "org.hsqldb.jdbcDriver";

	/**
	 * fichiers db
	 */
	private String database = "jdbc:hsqldb:file:db/coursdb";

	/**
	 * utilisateur qui se connecte � la base de donn�es
	 */
	private String user = "sa";

	/**
	 * mot de passe pour se connecter � la base de donn�es
	 */
	private String password = "";

	/**
	 * Connexion � la base de donn�e
	 */
	public void connexionBD() {
		try {
			Class.forName(jdbcDriver).newInstance();
		} catch (InstantiationException e) {
			System.out.println("ERROR: failed to load HSQLDB JDBC driver.");
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}

		try {
			connexion = DriverManager.getConnection(database, user, password);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	
	
	/**
	 * Arrête correctement HyperSQL
	 * 
	 * @throws SQLException
	 */
	public void arretBD() throws SQLException {
		Statement st = connexion.createStatement();
		st.execute("SHUTDOWN");
		connexion.close();

	}

	public Connection getConnexion() {
		return connexion;
	}

	public static void main(String[] args) {
		BDCours bdcours = new BDCours();
		bdcours.connexionBD();
		try {
			Statement s = bdcours.getConnexion().createStatement();
			s.executeUpdate("CREATE TABLE ETUDIANT (nom char(30), prenom char(30), email char(30),   primary key (email));"
					+ "CREATE TABLE MODULE (id_module int, nom char(30), responsable char(30), primary key (id_module));"
					+ "CREATE TABLE GROUPE (id_groupe int, id_module int, nom char(30), encadrant char(30), primary key(id_groupe));"
					+ "CREATE TABLE EVALUATION (id_evaluation int, id_module int, nom char(30), date char(30), mode_evaluation char(30), primary key(id_evaluation));"
					+ "CREATE TABLE NOTE (id_evaluation int, date_correction char(30), email char(30), note int, correcteur char(30), estdefinitive boolean, primary key(id_evaluation, email));"
					+ "CREATE TABLE REPARTITION_GROUPE (email char(30), id_groupe int, primary key(email,id_groupe));"
					);
			
			s = bdcours.getConnexion().createStatement();
			s.executeUpdate("ALTER TABLE GROUPE ADD CONSTRAINT C1 FOREIGN KEY(id_module) REFERENCES MODULE(id_module) on delete cascade;"
					+ "ALTER TABLE EVALUATION ADD CONSTRAINT C2 FOREIGN KEY(id_module) REFERENCES MODULE(id_module) on delete cascade;"
					+ "ALTER TABLE NOTE ADD CONSTRAINT C3 FOREIGN KEY(id_evaluation) REFERENCES EVALUATION(id_evaluation) on delete cascade;"
					+ "ALTER TABLE NOTE ADD CONSTRAINT C4 FOREIGN KEY(email) REFERENCES ETUDIANT(email) on delete cascade;"
					+ "ALTER TABLE REPARTITION_GROUPE ADD CONSTRAINT C5 FOREIGN KEY(email) REFERENCES ETUDIANT(email) on delete cascade;"
					+ "ALTER TABLE REPARTITION_GROUPE ADD CONSTRAINT C6 FOREIGN KEY(id_groupe) REFERENCES GROUPE(id_groupe) on delete cascade;"
					);
	
		bdcours.arretBD();
			System.out.println("terminé !");
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}
